--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Data Schema For Version 0.0.0.0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderDelete]
(
	@Original_OrderID Int
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[Orders] 
WHERE 
	[OrderID] = @Original_OrderID ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByOrderPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByOrderPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_OrderSelectByOrderPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM 
[Orders]
WHERE

	[Orders].[OrderID] IN (SELECT [OrderID] 
											FROM OpenXML(@hDoc, '//Orders', 2) 
											WITH ([OrderID] char(36) 'OrderID')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByOrderSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByOrderSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelectByOrderSinglePk]
(
	@OrderID Int
)
AS

SELECT 
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM
[Orders]
WHERE 
[Orders].[OrderID] = @OrderID 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_OrderInsert]
(
	@ShipVia Int = default,
	@ShipRegion NVarChar(15) = default,
	@ShipPostalCode NVarChar(10) = default,
	@ShippedDate DateTime = default,
	@ShipName NVarChar(40) = default,
	@ShipCountry NVarChar(15) = default,
	@ShipCity NVarChar(15) = default,
	@ShipAddress NVarChar(60) = default,
	@RequiredDate DateTime = default,
	@OrderID Int = default,
	@OrderDate DateTime = default,
	@Freight Money = default,
	@EmployeeID Int = default,
	@CustomerID NChar(5) = default,
	@created_date datetime,
	@created_by varchar (50),
	@modified_by varchar (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@OrderID < 0) SET @OrderID = NULL;
if ((@OrderID IS NULL))
BEGIN

INSERT INTO [Orders]
(
[CustomerID],
[EmployeeID],
[Freight],
[OrderDate],
[RequiredDate],
[ShipAddress],
[ShipCity],
[ShipCountry],
[ShipName],
[ShippedDate],
[ShipPostalCode],
[ShipRegion],
[ShipVia],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@CustomerID,
	@EmployeeID,
	@Freight,
	@OrderDate,
	@RequiredDate,
	@ShipAddress,
	@ShipCity,
	@ShipCountry,
	@ShipName,
	@ShippedDate,
	@ShipPostalCode,
	@ShipRegion,
	@ShipVia,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);

END
ELSE
BEGIN
SET identity_insert [Orders] on
INSERT INTO [Orders]
(
[OrderID],
[CustomerID],
[EmployeeID],
[Freight],
[OrderDate],
[RequiredDate],
[ShipAddress],
[ShipCity],
[ShipCountry],
[ShipName],
[ShippedDate],
[ShipPostalCode],
[ShipRegion],
[ShipVia],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@OrderID,
	@CustomerID,
	@EmployeeID,
	@Freight,
	@OrderDate,
	@RequiredDate,
	@ShipAddress,
	@ShipCity,
	@ShipCountry,
	@ShipName,
	@ShippedDate,
	@ShipPostalCode,
	@ShipRegion,
	@ShipVia,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);
SET identity_insert [Orders] off
END


SELECT 
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM
[Orders]
WHERE
	[Orders].[OrderID] = SCOPE_IDENTITY();
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderPagingSelect]
(
	@page int, -- page number selected by the user
	@pageSize int, -- number of items on the page
	@orderByColumn varchar(100), -- name of column to order things by
	@ascending bit, -- order column ascending or descending
	@filter varchar(100) = null, -- filter statement passed in to determine like criteria on order by column
	@count int out -- number of items in the collection
)
AS

SET NOCOUNT ON;


CREATE TABLE #tmpTable
(
[OrderID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[orderid]
)
SELECT
	[Orders].[orderid]
FROM
[Orders]
WHERE
	(@orderByColumn = 'ShipVia' and (((@filter is null) or ([Orders].[ShipVia] is null)) or (@filter is not null and [Orders].[ShipVia] = @filter)))
or
	(@orderByColumn = 'ShipRegion' and (((@filter is null) or ([Orders].[ShipRegion] is null)) or (@filter is not null and [Orders].[ShipRegion] LIKE @filter)))
or
	(@orderByColumn = 'ShipPostalCode' and (((@filter is null) or ([Orders].[ShipPostalCode] is null)) or (@filter is not null and [Orders].[ShipPostalCode] LIKE @filter)))
or
	(@orderByColumn = 'ShippedDate' and (((@filter is null) or ([Orders].[ShippedDate] is null)) or (@filter is not null and [Orders].[ShippedDate] = @filter)))
or
	(@orderByColumn = 'ShipName' and (((@filter is null) or ([Orders].[ShipName] is null)) or (@filter is not null and [Orders].[ShipName] LIKE @filter)))
or
	(@orderByColumn = 'ShipCountry' and (((@filter is null) or ([Orders].[ShipCountry] is null)) or (@filter is not null and [Orders].[ShipCountry] LIKE @filter)))
or
	(@orderByColumn = 'ShipCity' and (((@filter is null) or ([Orders].[ShipCity] is null)) or (@filter is not null and [Orders].[ShipCity] LIKE @filter)))
or
	(@orderByColumn = 'ShipAddress' and (((@filter is null) or ([Orders].[ShipAddress] is null)) or (@filter is not null and [Orders].[ShipAddress] LIKE @filter)))
or
	(@orderByColumn = 'RequiredDate' and (((@filter is null) or ([Orders].[RequiredDate] is null)) or (@filter is not null and [Orders].[RequiredDate] = @filter)))
or
	(@orderByColumn = 'OrderID' and (((@filter is null) or ([Orders].[OrderID] is null)) or (@filter is not null and [Orders].[OrderID] = @filter)))
or
	(@orderByColumn = 'OrderDate' and (((@filter is null) or ([Orders].[OrderDate] is null)) or (@filter is not null and [Orders].[OrderDate] = @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([Orders].[EmployeeID] is null)) or (@filter is not null and [Orders].[EmployeeID] = @filter)))
or
	(@orderByColumn = 'CustomerID' and (((@filter is null) or ([Orders].[CustomerID] is null)) or (@filter is not null and [Orders].[CustomerID] LIKE @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[Orders]
WHERE
	(@orderByColumn = 'ShipVia' and (((@filter is null) or ([Orders].[ShipVia] is null)) or (@filter is not null and [Orders].[ShipVia] = @filter)))
or
	(@orderByColumn = 'ShipRegion' and (((@filter is null) or ([Orders].[ShipRegion] is null)) or (@filter is not null and [Orders].[ShipRegion] LIKE @filter)))
or
	(@orderByColumn = 'ShipPostalCode' and (((@filter is null) or ([Orders].[ShipPostalCode] is null)) or (@filter is not null and [Orders].[ShipPostalCode] LIKE @filter)))
or
	(@orderByColumn = 'ShippedDate' and (((@filter is null) or ([Orders].[ShippedDate] is null)) or (@filter is not null and [Orders].[ShippedDate] = @filter)))
or
	(@orderByColumn = 'ShipName' and (((@filter is null) or ([Orders].[ShipName] is null)) or (@filter is not null and [Orders].[ShipName] LIKE @filter)))
or
	(@orderByColumn = 'ShipCountry' and (((@filter is null) or ([Orders].[ShipCountry] is null)) or (@filter is not null and [Orders].[ShipCountry] LIKE @filter)))
or
	(@orderByColumn = 'ShipCity' and (((@filter is null) or ([Orders].[ShipCity] is null)) or (@filter is not null and [Orders].[ShipCity] LIKE @filter)))
or
	(@orderByColumn = 'ShipAddress' and (((@filter is null) or ([Orders].[ShipAddress] is null)) or (@filter is not null and [Orders].[ShipAddress] LIKE @filter)))
or
	(@orderByColumn = 'RequiredDate' and (((@filter is null) or ([Orders].[RequiredDate] is null)) or (@filter is not null and [Orders].[RequiredDate] = @filter)))
or
	(@orderByColumn = 'OrderID' and (((@filter is null) or ([Orders].[OrderID] is null)) or (@filter is not null and [Orders].[OrderID] = @filter)))
or
	(@orderByColumn = 'OrderDate' and (((@filter is null) or ([Orders].[OrderDate] is null)) or (@filter is not null and [Orders].[OrderDate] = @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([Orders].[EmployeeID] is null)) or (@filter is not null and [Orders].[EmployeeID] = @filter)))
or
	(@orderByColumn = 'CustomerID' and (((@filter is null) or ([Orders].[CustomerID] is null)) or (@filter is not null and [Orders].[CustomerID] LIKE @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Orders] ON #tmpTable.[orderid] = [ORDERS].[orderid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectBySearchAnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectBySearchAnd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelectBySearchAnd]
(
	@ShipVia Int = null, 
	@ShipRegion NVarChar(15) = null, 
	@ShipPostalCode NVarChar(10) = null, 
	@ShippedDate DateTime = null, 
	@ShipName NVarChar(40) = null, 
	@ShipCountry NVarChar(15) = null, 
	@ShipCity NVarChar(15) = null, 
	@ShipAddress NVarChar(60) = null, 
	@RequiredDate DateTime = null, 
	@OrderID Int = null, 
	@OrderDate DateTime = null, 
	@EmployeeID Int = null, 
	@CustomerID NChar(5) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[OrderID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[orderid]
)
SELECT
	[Orders].[orderid]
FROM
[Orders]
WHERE
((@ShipVia is null) OR ([Orders].[ShipVia] = @ShipVia)) AND
((@ShipRegion is null) OR ([Orders].[ShipRegion] LIKE @ShipRegion)) AND
((@ShipPostalCode is null) OR ([Orders].[ShipPostalCode] LIKE @ShipPostalCode)) AND
((@ShippedDate is null) OR ([Orders].[ShippedDate] = @ShippedDate)) AND
((@ShipName is null) OR ([Orders].[ShipName] LIKE @ShipName)) AND
((@ShipCountry is null) OR ([Orders].[ShipCountry] LIKE @ShipCountry)) AND
((@ShipCity is null) OR ([Orders].[ShipCity] LIKE @ShipCity)) AND
((@ShipAddress is null) OR ([Orders].[ShipAddress] LIKE @ShipAddress)) AND
((@RequiredDate is null) OR ([Orders].[RequiredDate] = @RequiredDate)) AND
((@OrderID is null) OR ([Orders].[OrderID] = @OrderID)) AND
((@OrderDate is null) OR ([Orders].[OrderDate] = @OrderDate)) AND
((@EmployeeID is null) OR ([Orders].[EmployeeID] = @EmployeeID)) AND
((@CustomerID is null) OR ([Orders].[CustomerID] LIKE @CustomerID))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Orders]
WHERE
((@ShipVia is null) OR ([Orders].[ShipVia] = @ShipVia)) AND
((@ShipRegion is null) OR ([Orders].[ShipRegion] LIKE @ShipRegion)) AND
((@ShipPostalCode is null) OR ([Orders].[ShipPostalCode] LIKE @ShipPostalCode)) AND
((@ShippedDate is null) OR ([Orders].[ShippedDate] = @ShippedDate)) AND
((@ShipName is null) OR ([Orders].[ShipName] LIKE @ShipName)) AND
((@ShipCountry is null) OR ([Orders].[ShipCountry] LIKE @ShipCountry)) AND
((@ShipCity is null) OR ([Orders].[ShipCity] LIKE @ShipCity)) AND
((@ShipAddress is null) OR ([Orders].[ShipAddress] LIKE @ShipAddress)) AND
((@RequiredDate is null) OR ([Orders].[RequiredDate] = @RequiredDate)) AND
((@OrderID is null) OR ([Orders].[OrderID] = @OrderID)) AND
((@OrderDate is null) OR ([Orders].[OrderDate] = @OrderDate)) AND
((@EmployeeID is null) OR ([Orders].[EmployeeID] = @EmployeeID)) AND
((@CustomerID is null) OR ([Orders].[CustomerID] LIKE @CustomerID))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Orders] ON #tmpTable.[orderid] = [ORDERS].[orderid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderUpdate]
(
	@ShipVia Int,
	@ShipRegion NVarChar(15),
	@ShipPostalCode NVarChar(10),
	@ShippedDate DateTime,
	@ShipName NVarChar(40),
	@ShipCountry NVarChar(15),
	@ShipCity NVarChar(15),
	@ShipAddress NVarChar(60),
	@RequiredDate DateTime,
	@OrderDate DateTime,
	@Freight Money,
	@EmployeeID Int,
	@CustomerID NChar(5),
	@modified_by varchar (50),
	@modified_date datetime = null,
	@Original_OrderID Int,
	@Original_time_stamp timestamp

)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[Orders] 
SET
[CustomerID] = @CustomerID,
[EmployeeID] = @EmployeeID,
[Freight] = @Freight,
[OrderDate] = @OrderDate,
[RequiredDate] = @RequiredDate,
[ShipAddress] = @ShipAddress,
[ShipCity] = @ShipCity,
[ShipCountry] = @ShipCountry,
[ShipName] = @ShipName,
[ShippedDate] = @ShippedDate,
[ShipPostalCode] = @ShipPostalCode,
[ShipRegion] = @ShipRegion,
[ShipVia] = @ShipVia,
[modified_by] = @modified_by,
[modified_date] = @modified_date

WHERE
	[Orders].[OrderID] = @Original_OrderID AND
	[Orders].[time_stamp] = @Original_time_stamp


SELECT
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]
FROM 
[Orders]
WHERE
	[Orders].[OrderID] = @Original_OrderID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelectByCreatedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM
[Orders]
WHERE
((([Orders].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Orders].[created_date])) AND 
((([Orders].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Orders].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelectByModifiedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM
[Orders]
WHERE
((([Orders].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Orders].[modified_date])) AND 
((([Orders].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Orders].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectBySearchOr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectBySearchOr]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelectBySearchOr]
(
	@ShipVia Int = null, 
	@ShipRegion NVarChar(15) = null, 
	@ShipPostalCode NVarChar(10) = null, 
	@ShippedDate DateTime = null, 
	@ShipName NVarChar(40) = null, 
	@ShipCountry NVarChar(15) = null, 
	@ShipCity NVarChar(15) = null, 
	@ShipAddress NVarChar(60) = null, 
	@RequiredDate DateTime = null, 
	@OrderID Int = null, 
	@OrderDate DateTime = null, 
	@EmployeeID Int = null, 
	@CustomerID NChar(5) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[OrderID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[orderid]
)
SELECT
	[Orders].[orderid]
FROM
[Orders]
WHERE
((@ShipVia is null) OR ([Orders].[ShipVia] = @ShipVia)) OR
((@ShipRegion is null) OR ([Orders].[ShipRegion] LIKE @ShipRegion)) OR
((@ShipPostalCode is null) OR ([Orders].[ShipPostalCode] LIKE @ShipPostalCode)) OR
((@ShippedDate is null) OR ([Orders].[ShippedDate] = @ShippedDate)) OR
((@ShipName is null) OR ([Orders].[ShipName] LIKE @ShipName)) OR
((@ShipCountry is null) OR ([Orders].[ShipCountry] LIKE @ShipCountry)) OR
((@ShipCity is null) OR ([Orders].[ShipCity] LIKE @ShipCity)) OR
((@ShipAddress is null) OR ([Orders].[ShipAddress] LIKE @ShipAddress)) OR
((@RequiredDate is null) OR ([Orders].[RequiredDate] = @RequiredDate)) OR
((@OrderID is null) OR ([Orders].[OrderID] = @OrderID)) OR
((@OrderDate is null) OR ([Orders].[OrderDate] = @OrderDate)) OR
((@EmployeeID is null) OR ([Orders].[EmployeeID] = @EmployeeID)) OR
((@CustomerID is null) OR ([Orders].[CustomerID] LIKE @CustomerID))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Orders]
WHERE
((@ShipVia is null) OR ([Orders].[ShipVia] = @ShipVia)) OR
((@ShipRegion is null) OR ([Orders].[ShipRegion] LIKE @ShipRegion)) OR
((@ShipPostalCode is null) OR ([Orders].[ShipPostalCode] LIKE @ShipPostalCode)) OR
((@ShippedDate is null) OR ([Orders].[ShippedDate] = @ShippedDate)) OR
((@ShipName is null) OR ([Orders].[ShipName] LIKE @ShipName)) OR
((@ShipCountry is null) OR ([Orders].[ShipCountry] LIKE @ShipCountry)) OR
((@ShipCity is null) OR ([Orders].[ShipCity] LIKE @ShipCity)) OR
((@ShipAddress is null) OR ([Orders].[ShipAddress] LIKE @ShipAddress)) OR
((@RequiredDate is null) OR ([Orders].[RequiredDate] = @RequiredDate)) OR
((@OrderID is null) OR ([Orders].[OrderID] = @OrderID)) OR
((@OrderDate is null) OR ([Orders].[OrderDate] = @OrderDate)) OR
((@EmployeeID is null) OR ([Orders].[EmployeeID] = @EmployeeID)) OR
((@CustomerID is null) OR ([Orders].[CustomerID] LIKE @CustomerID))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Orders] ON #tmpTable.[orderid] = [ORDERS].[orderid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipVia' THEN [Orders].[ShipVia] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipRegion' THEN [Orders].[ShipRegion] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipPostalCode' THEN [Orders].[ShipPostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShippedDate' THEN [Orders].[ShippedDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipName' THEN [Orders].[ShipName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCountry' THEN [Orders].[ShipCountry] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipCity' THEN [Orders].[ShipCity] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ShipAddress' THEN [Orders].[ShipAddress] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'RequiredDate' THEN [Orders].[RequiredDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderID' THEN [Orders].[OrderID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'OrderDate' THEN [Orders].[OrderDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Orders].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CustomerID' THEN [Orders].[CustomerID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Orders].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Orders].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Orders].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Orders].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_OrderSelect]
AS

SET NOCOUNT ON;

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM 
[Orders]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByCustomerPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByCustomerPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_OrderSelectByCustomerPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM 
[Orders]
WHERE
	[Orders].[CustomerID] IN (SELECT [CustomerID]
				FROM OpenXML(@hDoc, '//Customers', 2)
				WITH ([CustomerID] NChar(5) 'CustomerID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_OrderSelectByEmployeePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM 
[Orders]
WHERE
	[Orders].[EmployeeID] IN (SELECT [EmployeeID]
				FROM OpenXML(@hDoc, '//Employees', 2)
				WITH ([EmployeeID] Int 'EmployeeID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_OrderSelectByShipperPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_OrderSelectByShipperPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_OrderSelectByShipperPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Orders].[shipvia],
	[Orders].[shipregion],
	[Orders].[shippostalcode],
	[Orders].[shippeddate],
	[Orders].[shipname],
	[Orders].[shipcountry],
	[Orders].[shipcity],
	[Orders].[shipaddress],
	[Orders].[requireddate],
	[Orders].[orderid],
	[Orders].[orderdate],
	[Orders].[freight],
	[Orders].[employeeid],
	[Orders].[customerid],
	[Orders].[created_by],
	[Orders].[created_date],
	[Orders].[modified_by],
	[Orders].[modified_date],
	[Orders].[time_stamp]

FROM 
[Orders]
WHERE
	[Orders].[ShipVia] IN (SELECT [ShipperID]
				FROM OpenXML(@hDoc, '//Shippers', 2)
				WITH ([ShipperID] Int 'ShipperID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

